use std::env;
use dotenv::dotenv;
use sea_orm::{ Database, DatabaseConnection, DatabaseTransaction, DbBackend, EntityTrait, FromQueryResult, PaginatorTrait, Select, Statement };
use axum::Json;
use serde_json::{Value, json};

use serde::{ Deserialize, Serialize };
#[derive(Debug, Deserialize, Serialize, FromQueryResult)]
pub struct TableColumn {
	label:String
}
use super::{ code, query };

pub mod fun;
pub mod cust;
pub mod uuid;
pub mod crud;
pub mod bus;
pub mod number;
pub mod delete;
pub mod sql;

pub async fn connect( ) -> Result<DatabaseConnection, Json<Value>> {
	dotenv( ).ok( );
    let env_dbname = env::var("DATABASE").unwrap_or_default();
    let env_db_url = env::var("DATABASE_URL").unwrap_or_default();
    let dbname = if env_dbname.is_empty() { config::DATABASE } else { &env_dbname };
    let url = if env_db_url.is_empty() {config::DATABASE_URL.to_owned() + dbname } else { env_db_url };
    match Database::connect( url ).await {
        Ok(f) => Ok(f),
        Err(e) => Err(code::sys_("MySQL 数据库连接错误：".to_string() + &e.to_string()))
    }
}
pub async fn connect_db(dbname:&str) -> Result<DatabaseConnection, Json<Value>> {
    let env_db_url = env::var("DATABASE_PR").unwrap_or_default();
    let url = if env_db_url.is_empty() {config::DATABASE_URL.to_owned() + dbname } else { env_db_url + dbname };
    let url = if dbname.contains("://") { dbname } else { &url };
    match Database::connect( url ).await {
        Ok(f) => Ok(f),
        Err(e) => Err(code::sys_("MySQL 数据库连接错误：".to_string() + &e.to_string()))
    }
}
pub async fn send_paging_conn<S:EntityTrait>(selector: Select<S>, param:Value, conn:&DatabaseConnection, ext:Value) -> Result<Json<Value>, Json<Value>>{
    let page = query::u64_op(&param, "page")?;
    let size = query::u64_op(&param, "size")?;

    let page = page.unwrap_or(1);
    let size = size.unwrap_or(10);

    let paging = selector.into_json().paginate(conn, size);
    let result = paging.fetch_page( page-1 ).await;
    let total_nums = match paging.num_items( ).await {
        Ok(f) => f,
        Err(_) => 0
    };
    let total_pages = match paging.num_pages( ).await {
        Ok(f) => f,
        Err(_) => 0
    };
    
    match result {
        Ok(f) => {
            let mut data = json!({ 
                "size": size,
                "page": page,
                "total_items": total_nums,
                "total_pages": total_pages,
                "data": f,
            });
            if !ext.is_null() {
                data["ext"] = ext
            }
             code::data(data)
        },
        Err(e) => code::sys(e.to_string())
    }
}
pub async fn send_paging<S:EntityTrait>(selector: Select<S>, param:Value) -> Result<Json<Value>, Json<Value>>{
    let conn = connect().await?;
    send_paging_conn(selector, param, &conn, json!(null)).await
}
pub async fn send_sql_ok_<T, E:ToString>( conn:&DatabaseConnection, result:Result<T, E>, msg:&str ) -> Result<Json<Value>, Json<Value>>{
    match result {
        Ok(_f) => code::ok(),
        Err(e) => {
            let str = e.to_string();
            if str.contains("1062 (23000)"){
                if !msg.is_empty() { return  code::err( msg.to_owned() + "已存在，不能重复添加！"); }
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[3].split(".").collect::<Vec<&str>>();
                let table_name = d[0];
                let column_name = d[1];
                let stmt = Statement::from_sql_and_values(
                    DbBackend::MySql,
                    r#"select column_comment label from information_schema.columns where table_name= ? and column_name= ?"#,
                    [ table_name.into(), column_name.into() ]								
                );
                let result = TableColumn::find_by_statement(stmt).one(conn).await;
                match result {
                    Ok(f) => {
                        if let Some(t) = f {
                            return code::err(t.label + ": " + s[1] + " 已存在，不能重复添加！")
                        }else {
                            return code::err(column_name.to_owned() + " 值已存在，不能重复添加！")
                        }
                    },
                    Err(_f) => return code::err(column_name.to_owned() + " 值已存在，不能重复添加！")
                }
            }else if str.contains("1264 (22003)"){
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[1].split("'").collect::<Vec<&str>>();
                code::err(d[0].to_owned() + " 超出限制值范围")
            }else if str.contains("1406 (22001)"){
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[1].split("'").collect::<Vec<&str>>();
                code::err(d[0].to_owned() + " 数据长度超出限制范围")
            }else{
                code::sys(str)
            }
        }
    }
}
pub async fn send_sql_ok<T, E:ToString>( conn:&DatabaseConnection, result:Result<T, E> ) -> Result<Json<Value>, Json<Value>>{
    send_sql_ok_(conn, result, "").await
}
pub async fn send_bus_ok<T, E:ToString>( conn:&DatabaseTransaction, result:Result<T, E> ) -> Result<Json<Value>, Json<Value>>{
    match result {
        Ok(_f) => code::ok(),
        Err(e) => {
            let str = e.to_string();
            if str.contains("1062 (23000)"){
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[3].split(".").collect::<Vec<&str>>();
                let table_name = d[0];
                let column_name = d[1];
                let stmt = Statement::from_sql_and_values(
                    DbBackend::MySql,
                    r#"select column_comment label from information_schema.columns where table_name= ? and column_name= ?"#,
                    [ table_name.into(), column_name.into() ]								
                );
                let result = TableColumn::find_by_statement(stmt).one(conn).await;
                match result {
                    Ok(f) => {
                        if let Some(t) = f {
                            return code::err(t.label + ": " + s[1] + " 已存在，不能重复添加！")
                        }else {
                            return code::err(column_name.to_owned() + " 值已存在，不能重复添加！")
                        }
                    },
                    Err(_f) => return code::err(column_name.to_owned() + " 值已存在，不能重复添加！")
                }
            }else if str.contains("1264 (22003)"){
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[1].split("'").collect::<Vec<&str>>();
                code::err(d[0].to_owned() + " 超出限制值范围")
            }else if str.contains("1406 (22001)"){
                let s = str.split("'").collect::<Vec<&str>>();
                let d = s[1].split("'").collect::<Vec<&str>>();
                code::err(d[0].to_owned() + " 数据长度超出限制范围")
            }else{
                code::sys(str)
            }
        }
    }
}